package dbLayer;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

import modelLayer.Class;
import modelLayer.Staff;

public class DBClass {
	
	private Connection con;
	private DBStaff dbStaff = new DBStaff();
	
	public DBClass () {
		this.con = DBConnection.getInstance().getDBcon();
	}
	
	public List<Class> getAllClasses () {
		
		ResultSet results;
		List<Class> classes = new ArrayList<>();
		
		try {
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Classes");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			while (results.next()) {
				Class cl = new Class();
				cl = buildObject(results);
				System.out.println(cl);
				classes.add(cl);
			}
		}
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return classes;
	}
	
	public Class findClassID (int ID) {
		
		ResultSet results;
		Class cl = null;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Classes WHERE ID = ?");
			statement.setInt(1, ID);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				cl = buildObject(results);
				System.out.println(cl);
				
			}
			
		}
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		
		return cl;
	}
	
	public List<Class> findClassName (String name) {
		
		ResultSet results;
		List<Class> matchingClasses = new ArrayList<>();
		
		try {
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Classes WHERE name = ?");
			statement.setString(1, "%" + name + "%");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			while (results.next()) {
				Class cl = new Class();
				cl = buildObject(results);
				System.out.println(cl);
				matchingClasses.add(cl);
			}
			statement.close();
		}
		catch (Exception e) {
			System.out.println("Query exception: "  +e);
		}
		
		return matchingClasses;
	}

	public int HighestID() {
		
		int maxID = 0;
		
		ResultSet results;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT MAX(ID) FROM Classes");
			statement.setQueryTimeout(5);
			
			results = statement.executeQuery();
			
			while (results.next()) {
				
				maxID = results.getInt(1);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exception: "  +e);
		}
		
		return maxID;
		
	}

	public void addClass(Class cl) {
		
		//To test whether or not there is a staff member added to the class
		boolean staffCheck;
				
		try {
				
			cl.getStaff();
				
			staffCheck = true;
					
		}
		catch (Exception e) {
				staffCheck = false;
				System.out.println("Exception: "  + e);
		}
		
		try {
			
			PreparedStatement statement = con.prepareStatement("INSERT INTO Classes VALUES (?,?)");
			statement.setString(1, cl.getName());
			if (staffCheck) {
				statement.setInt(2, cl.getStaff().getID());
			}
			else {
				statement.setNull(2, java.sql.Types.INTEGER);
			}
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exception: "  +e);
		}
		
	}

	public void updateClass(Class cl) {
		
		//To test whether or not there is a staff member added to the class
		boolean staffCheck;
		
		try {
			
			cl.getStaff();
			
			staffCheck = true;
			
		}
		catch (Exception e) {
			staffCheck = false;
			System.out.println("Exception: "  + e);
		}
		
		try {
			
			PreparedStatement statement = con.prepareStatement("UPDATE Classes SET Name = ?, Staff = ? WHERE ID = ?");
			statement.setString(1, cl.getName());
			if (staffCheck) {
				statement.setInt(2, cl.getStaff().getID());
			}
			else {
				statement.setNull(2, java.sql.Types.INTEGER);
			}
			statement.setInt(3, cl.getID());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: "  + e);
		}
		
	}

	public void removeClass(int classID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("DELETE FROM Classes WHERE ID = ?");
			statement.setInt(1, classID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Query exception: "  +e);
		}
		
	}

	private Class buildObject(ResultSet results) {
		
		Class cl = null;
		Staff staff = null;
				
		try {
			
			if ( results.getInt("Staff") != 0) {
				staff = dbStaff.findStaffID(results.getInt("Staff"));
			}
			
			cl = new Class (results.getInt("ID"),
							results.getString("Name"),
							staff);
		}
		catch (Exception e) {
			System.out.println("Creation of the Class caused exception: " + e);
		}
		
		return cl;
	}
	

}
